package com.tangdi.Dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.tangdi.Utils;
import com.tangdi.Bean.CZGZ_DWCOUNT;
import com.tangdi.Bean.CZGZ_GZLB;
import com.tangdi.Bean.CZGZ_SJGZ;
import com.tangdi.Bean.CZGZ_ZGGZMX;
import com.tangdi.Bean.DW_DM_MC;
import com.tangdi.Bean.XM_BM_XM_MC;
import com.tangdi.common.DBConnect;

public class GongziMXImpl{ 
	private Logger logger = Logger.getLogger(GongziMXImpl.class);
	Connection con=DBConnect.getInstance().getConnection();
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	 

	//根据月份、单位代码、工资类别以及组装的select 列名，来查询工资明细
	public List<CZGZ_ZGGZMX> listGZMXReportByLB(String month,String dw_dm,String gz_lb_dm,List<XM_BM_XM_MC> bm_mc_list,String sql){
		List<CZGZ_ZGGZMX> list = new ArrayList<CZGZ_ZGGZMX>();
		StringBuffer sql_select = new StringBuffer(sql);
		
		sql_select.append(" FROM CZGZ_ZGGZMX WHERE TRIM(YF) = '"+ month +"' AND TRIM(DW_DM) = '"+ dw_dm +"' AND TRIM(gz_lb_dm) = '"+ gz_lb_dm +"'"
				+ " ORDER BY TRIM(BM_DM),TO_NUMBER(TRIM(ORD_NO))");
		
		String sql_run = sql_select.toString();
		//logger.info(sql_run);
		try{
			pstmt=con.prepareStatement(sql_run);
			rs=pstmt.executeQuery();
			while(rs.next()){
				CZGZ_ZGGZMX gz_mx = new CZGZ_ZGGZMX();
				gz_mx.setYF(rs.getString("YF"));
				gz_mx.setNME(rs.getString("NME"));
				for(int i = 0; i < bm_mc_list.size(); i++){
					Utils.setFieldReflect(gz_mx,CZGZ_ZGGZMX.class,bm_mc_list.get(i).getXM_BM(),rs.getString(bm_mc_list.get(i).getXM_BM()));
				}
				list.add(gz_mx);
			}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}
		

		return list;
	}
	
	//工资类别list
	public List<CZGZ_GZLB> listGZLBReport(String month){
		List<CZGZ_GZLB> list = new ArrayList<CZGZ_GZLB>();
		String sql="SELECT TRIM(BAT_NO) AS BAT_NO,TRIM(THD_BAT_NO) AS THD_BAT_NO,TRIM(YF) AS YF,TRIM(LB_BM) AS LB_BM,TRIM(LB_MC) AS LB_MC,TRIM(GZ_LX) AS GZ_LX,TRIM(GZ_LX_MC) AS GZ_LX_MC,TRIM(TP_BAT_NO) AS TP_BAT_NO,"
				+ "TRIM(LB_REMARK) AS LB_REMARK,TRIM(YEAR) AS YEAR,TRIM(MONTH) AS MONTH,TRIM(RCN_CDE) AS RCN_CDE from CZGZ_GZLB "
				+ "where trim(YF) = '"+ month +"'";
		//logger.info(sql);
		try{
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				CZGZ_GZLB lb = new CZGZ_GZLB();
				lb.setYF(rs.getString("YF"));
				lb.setLB_BM(rs.getString("LB_BM"));
				lb.setLB_MC(rs.getString("LB_MC"));
				list.add(lb);
			}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}
		

		return list;
	}
	
	//实际工资科目代码名称list 按工资类别
	public List<CZGZ_SJGZ> listSJGZReport(String month){
		List<CZGZ_SJGZ> list=new ArrayList<CZGZ_SJGZ>();
		String sql="SELECT TRIM(THD_BAT_NO) AS THD_BAT_NO,TRIM(BAT_NO) AS BAT_NO,TRIM(YF) AS YF,TRIM(DW_DM) AS DW_DM,TRIM(GZ_LB) AS GZ_LB,TRIM(XM_BM) AS XM_BM,TRIM(XM_MC) AS XM_MC,TRIM(XM_XZ) AS XM_XZ,"
				+ "TRIM(SEQ_NO) AS SEQ_NO,TRIM(GZ_LX) AS GZ_LX,TRIM(GZ_LX_MC) AS GZ_LX_MC,TRIM(TP_BAT_NO) AS TP_BAT_NO,TRIM(SF_REMARK) AS SF_REMARK,TRIM(YEAR) AS YEAR,TRIM(MONTH) AS MONTH,TRIM(RCN_CDE) AS RCN_CDE "
				+ "FROM CZGZ_SJGZ where YF = '"+ month +"' ORDER BY TRIM(GZ_LB) DESC,TRIM(SEQ_NO) ASC";
		//logger.info(sql);
		
		try{
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				CZGZ_SJGZ sjgz = new CZGZ_SJGZ();
				sjgz.setGZ_LB(rs.getString("GZ_LB"));
				sjgz.setXM_BM(rs.getString("XM_BM"));
				sjgz.setXM_MC(rs.getString("XM_MC"));
				list.add(sjgz);
			}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}
	
				return list;
	}
	
	//按工资类别、单位代码，单位名称，统计工资条数
	public List<CZGZ_DWCOUNT> listGZDWCount(String month){
		List<CZGZ_DWCOUNT> list=new ArrayList<CZGZ_DWCOUNT>();
		String sql="SELECT COUNT(1) AS COUNT,TRIM(GZ_LB_DM) AS GZ_LB_DM,TRIM(DW_DM) AS DW_DM,TRIM(DW_MC) AS DW_MC "
				+ " FROM CZGZ_ZGGZMX  WHERE YF = '"+ month +"'"
				+ " GROUP BY TRIM(GZ_LB_DM),TRIM(DW_DM),TRIM(DW_MC) ORDER BY TRIM(DW_DM) ASC,TRIM(GZ_LB_DM) DESC ";
		//logger.info(sql);
		
		try{
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				CZGZ_DWCOUNT gz_dwcount = new CZGZ_DWCOUNT();
				gz_dwcount.setDW_DM(rs.getString("DW_DM"));
				gz_dwcount.setDW_MC(rs.getString("DW_MC"));
				gz_dwcount.setGZ_LB_DM(rs.getString("GZ_LB_DM"));
				gz_dwcount.setCount(rs.getString("COUNT"));
				list.add(gz_dwcount);
			}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}
	
				return list;
	}
	
	//根据月份、单位代码、工资类别以及组装的select 列名，来查询工资明细
	public CZGZ_ZGGZMX getCountGZByLB_DW(String month,String dw_dm,String gz_lb_dm,List<XM_BM_XM_MC> bm_mc_list,String sql){
		CZGZ_ZGGZMX gzDW_Count = new CZGZ_ZGGZMX();
		StringBuffer sql_select = new StringBuffer(sql);
		
		sql_select.append(" FROM CZGZ_ZGGZMX WHERE TRIM(YF) = '"+ month +"' AND TRIM(DW_DM) = '"+ dw_dm +"' AND TRIM(gz_lb_dm) = '"+ gz_lb_dm +"'");
		
		String sql_run = sql_select.toString();
		//logger.info(sql_run);
		try{
			pstmt=con.prepareStatement(sql_run);
			rs=pstmt.executeQuery();
			while(rs.next()){
				for(int i = 0; i < bm_mc_list.size(); i++){
					Utils.setFieldReflect(gzDW_Count,CZGZ_ZGGZMX.class,bm_mc_list.get(i).getXM_BM(),rs.getString(bm_mc_list.get(i).getXM_BM()));
				}
			}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}
		

		return gzDW_Count;
	}

	//根据月份、单位代码、工资类别以及组装的select 列名，来查询工资明细
	public CZGZ_ZGGZMX getCountGZByLB_WorkCode(String month,String dw_dm,String gz_lb_dm,List<XM_BM_XM_MC> bm_mc_list,String sql,String work_code){
		CZGZ_ZGGZMX gzDW_Count = new CZGZ_ZGGZMX();
		StringBuffer sql_select = new StringBuffer(sql);
		
		sql_select.append(" FROM CZGZ_ZGGZMX WHERE TRIM(YF) = '"+ month +"' AND TRIM(DW_DM) = '"+ dw_dm +"' AND TRIM(GZ_LB_DM) = '"+ gz_lb_dm +"'");
		sql_select.append(" AND TRIM(WORK_STATUS_CDE) = '"+ work_code +"'");
		String sql_run = sql_select.toString();
		//logger.info(sql_run);
		try{
			pstmt=con.prepareStatement(sql_run);
			rs=pstmt.executeQuery();
			while(rs.next()){
				for(int i = 0; i < bm_mc_list.size(); i++){
					Utils.setFieldReflect(gzDW_Count,CZGZ_ZGGZMX.class,bm_mc_list.get(i).getXM_BM(),rs.getString(bm_mc_list.get(i).getXM_BM()));
				}
			}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}
		

		return gzDW_Count;
	}
	
	//根据月份 查询包含的单位编号 返回所有单位编号 distinct
	public List<DW_DM_MC> getListDW_DM_MC(String month){
		List<DW_DM_MC> list_DW_DM = new ArrayList<DW_DM_MC>();
		String sql_run = new String("SELECT DISTINCT TRIM(DW_DM) AS DW_DM, TRIM(DW_MC) AS DW_MC FROM CZGZ_ZGGZMX WHERE TRIM(YF) = '"+ month +"'");
		//logger.info(sql_run);
		try{
			pstmt=con.prepareStatement(sql_run);
			rs=pstmt.executeQuery();
			while(rs.next()){
				DW_DM_MC dm_mc = new DW_DM_MC();
				dm_mc.setDM(rs.getString(1));
				dm_mc.setMC(rs.getString(2));
				list_DW_DM.add(dm_mc);
				}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}

		return list_DW_DM;
	}
	
	//根据单位代码、 类别 统计人数 / 统计个税 /返回科目及科目代码
	public String getDWRSCount_ByLB(String month, String sql){
		String RS_Count = "0";
		String sql_run = new String(sql);
		//logger.info(sql_run);
		try{
			pstmt=con.prepareStatement(sql_run);
			rs=pstmt.executeQuery();
			while(rs.next()){
				RS_Count = rs.getString(1);
				}
			}catch(SQLException e){
				e.printStackTrace();
				}finally{
					if(pstmt!=null)
						try{
							pstmt.close();
							}catch(SQLException e){
								e.printStackTrace();
					}
				}

		return RS_Count;
	}
}
